# in python3.8
# you need to install [sqlite4 cmd2 textwrap3 prettytable] with pip
import sqlite3
import cmd
from textwrap import dedent
from prettytable import PrettyTable

DB_NAME = "vocabulary.db"

class WordDatabaseShell(cmd.Cmd):
    intro = dedent("""
        📖 英语单词数据库管理器
        输入 'help' 查看可用命令
        ↑/↓ 浏览历史 | Tab 补全命令
    """)
    prompt = "(words) > "

    def __init__(self):
        super().__init__()
        self.conn = sqlite3.connect(DB_NAME)
        self._create_table()

    def _create_table(self):
        """创建单词表"""
        self.conn.execute("""
        CREATE TABLE IF NOT EXISTS words (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            word TEXT UNIQUE NOT NULL
        )
        """)
        self.conn.commit()

    def _display_words(self, cursor):
        """美化输出单词列表"""
        pt = PrettyTable(["ID", "Word"])
        pt.align = "l"
        
        for row in cursor:
            pt.add_row(row)
        
        print(pt)
        print(f"找到 {len(pt._rows)} 个单词")

    def do_add(self, arg):
        """添加新单词: add"""
        word = input("请输入要添加的单词: ").strip().lower()
        if not word:
            print("❌ 单词不能为空")
            return
        
        try:
            self.conn.execute("INSERT INTO words (word) VALUES (?)", (word,))
            self.conn.commit()
            print(f"✅ 成功添加单词: {word}")
        except sqlite3.IntegrityError:
            print(f"❌ 单词 '{word}' 已存在")
        except Exception as e:
            print(f"❌ 发生错误: {str(e)}")

    def do_list(self, arg):
        """列出所有单词: list [asc/desc]"""
        order = "ASC" if "asc" in arg.lower() else "DESC"
        cursor = self.conn.execute(f"""
            SELECT id, word FROM words 
            ORDER BY word {order}
        """)
        self._display_words(cursor)

    def do_search(self, arg):
        """搜索单词: search <关键词>"""
        if not arg:
            print("❌ 请输入搜索关键词")
            return
        
        cursor = self.conn.execute("""
            SELECT id, word FROM words
            WHERE word LIKE ?
            ORDER BY word ASC
        """, (f"%{arg}%",))
        self._display_words(cursor)

    def do_edit(self, arg):
        """修改单词: edit <id>"""
        try:
            word_id = int(arg)
            # 获取原单词
            old_word = self.conn.execute(
                "SELECT word FROM words WHERE id = ?", 
                (word_id,)
            ).fetchone()
            
            if not old_word:
                print("❌ 该ID不存在")
                return
            
            new_word = input(f"原单词: {old_word[0]}\n新单词: ").strip().lower()
            if not new_word:
                print("⚠️ 未做修改")
                return
                
            try:
                self.conn.execute(
                    "UPDATE words SET word = ? WHERE id = ?",
                    (new_word, word_id)
                )
                self.conn.commit()
                print(f"✅ 成功修改 ID {word_id}")
            except sqlite3.IntegrityError:
                print(f"❌ 单词 '{new_word}' 已存在")
                
        except ValueError:
            print("❌ 请输入有效的数字ID")

    def do_delete(self, arg):
        """删除单词: delete <id>"""
        try:
            word_id = int(arg)
            # 显示目标单词
            target = self.conn.execute(
                "SELECT word FROM words WHERE id = ?", 
                (word_id,)
            ).fetchone()
            
            if not target:
                print("❌ 该ID不存在")
                return
                
            confirm = input(f"确认删除 ID {word_id} ({target[0]}) 吗？(y/N): ")
            if confirm.lower() == 'y':
                self.conn.execute("DELETE FROM words WHERE id = ?", (word_id,))
                self.conn.commit()
                print("✅ 删除成功")
        except ValueError:
            print("❌ 请输入有效的数字ID")

    def do_import(self, arg):
        """批量导入单词: import <文件名>"""
        try:
            with open(arg, 'r') as f:
                words = [line.strip().lower() for line in f if line.strip()]
                
            duplicates = 0
            new_words = 0
            
            for word in words:
                try:
                    self.conn.execute(
                        "INSERT INTO words (word) VALUES (?)", 
                        (word,)
                    )
                    new_words += 1
                except sqlite3.IntegrityError:
                    duplicates += 1
                    
            self.conn.commit()
            print(f"导入完成 | 新增: {new_words} | 重复: {duplicates}")
        except FileNotFoundError:
            print("❌ 文件不存在")

    def do_sql(self, arg):
        """执行原始SQL: sql <语句>"""
        try:
            cursor = self.conn.execute(arg)
            if cursor.description:  # 如果是查询语句
                self._display_words(cursor)
            else:
                print(f"操作成功，影响 {cursor.rowcount} 行")
            self.conn.commit()
        except Exception as e:
            print(f"❌ SQL错误：{str(e)}")

    def do_quit(self, arg):
        """退出程序: quit"""
        print("🛑 已退出数据库管理")
        self.conn.close()
        return True

    # 命令别名
    do_exit = do_quit
    do_q = do_quit

if __name__ == "__main__":
    WordDatabaseShell().cmdloop()
